package com.example.car.db

import android.content.ContentValues
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.example.car.bean.TitleInfo


class SubDbHelper @JvmOverloads constructor(
    context: Context?, name: String?, factory: SQLiteDatabase.CursorFactory?, version: Int
) : SQLiteOpenHelper(context, name, factory, version) {

    companion object {
        private var sHelper: SubDbHelper? = null
        private const val DB_NAME = "data.db"
        private const val VERSION = 1

        const val TABLE_SUB_CHANNEL = "sub_channel"
        const val ID = "id"
        const val TITLE = "title"
        const val TITLE_PY = "title_py"
        const val IS_SUB = "is_sub"


        // 创建单例，供外部调用该类的增删改查方法
        @Synchronized
        fun getInstance(context: Context): SubDbHelper {
            if (sHelper == null) {
                sHelper = SubDbHelper(context, DB_NAME, null, VERSION)
            }
            return sHelper!!
        }
    }


    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(
            """
            create table ${TABLE_SUB_CHANNEL}(
                    ${ID} integer primary key autoincrement, 
                    ${TITLE} text UNIQUE,
                    ${TITLE_PY} text,
                    ${IS_SUB} integer default 0
            )""".trimIndent()
        )
    }

    override fun onUpgrade(db: SQLiteDatabase, p1: Int, p2: Int) {
        // 升级数据库时调用（如删除旧表重建）
        db.execSQL("DROP TABLE IF EXISTS ${TABLE_SUB_CHANNEL}")
        onCreate(db)
    }

    // 更新订阅
    fun updateSub(id: Int, is_sub: Int): Int {
        val db = writableDatabase
        val values = ContentValues().apply {
            put(IS_SUB, is_sub)
        }
        // 执行更新操作，返回受影响的行数
        val row = db.update(
            TABLE_SUB_CHANNEL, values, "${ID}=?", arrayOf(id.toString())
        )
        db.close()
        return row
    }

    // 插入单条订阅
    fun insertSingleData(title: String, title_py: String, is_sub: Int): Long {
        val db = writableDatabase
        val values = ContentValues().apply {
            put(TITLE, title)
            put(TITLE_PY, title_py)
            put(IS_SUB, is_sub)
        }
        // 插入数据，返回新记录的 ID（-1 表示失败）
        // 如果title存在则忽略
        val newRowId = db.insertWithOnConflict(
            TABLE_SUB_CHANNEL, null, values, SQLiteDatabase.CONFLICT_IGNORE
        )
        db.close()
        return newRowId
    }

    // 插入多条数据（事务处理，确保原子性）
    fun insertMultipleData(titls: List<TitleInfo>): Boolean {
        val db = writableDatabase
        return try {
            db.beginTransaction() // 开启事务
            for (item in titls) {
                val values = ContentValues().apply {
                    put(TITLE, item.title)
                    put(TITLE_PY, item.title_py)
                    put(IS_SUB, item.is_sub)
                }
                // 如果title存在则忽略
                val rowId = db.insertWithOnConflict(
                    TABLE_SUB_CHANNEL, null, values, SQLiteDatabase.CONFLICT_IGNORE
                )
            }
            db.setTransactionSuccessful() // 标记事务成功
            true
        } catch (e: Exception) {
            e.printStackTrace()
            false
        } finally {
            db.endTransaction() // 结束事务（自动提交或回滚）
            db.close()
        }
    }

    // 根据ID删除单条数据
    fun deleteDataById(db: SQLiteDatabase, userId: Int): Int {
        val db = writableDatabase
        // where 条件：删除 id = userId 的记录
        val whereClause = "${ID} = ?"
        val whereArgs = arrayOf(userId.toString()) // 用占位符避免 SQL 注入

        // 执行删除，返回受影响的行数（0 表示未删除，1 表示删除成功）
        val rowsDeleted = db.delete(
            TABLE_SUB_CHANNEL, whereClause, whereArgs
        )
        db.close()
        return rowsDeleted
    }

    // 查询是否订阅的数据
    fun queryMultipleData(is_sub: Int? = null): List<TitleInfo> {
        val subChannels = mutableListOf<TitleInfo>()
        val db = readableDatabase
        var whereClause: String? = null
        var whereArgs: Array<String>? = null
        if (is_sub != null) {
            whereClause = "${IS_SUB} = ?"
            whereArgs = arrayOf(is_sub.toString())
        }

        val cursor: Cursor = db.query(
            TABLE_SUB_CHANNEL,
            arrayOf(ID, TITLE, TITLE_PY, IS_SUB),
            whereClause,
            whereArgs,
            null,
            null,
            null
        )

        // 遍历游标（同上面的逻辑）
        if (cursor.moveToFirst()) {
            do {
                val id = cursor.getInt(cursor.getColumnIndexOrThrow(ID))
                val title = cursor.getString(cursor.getColumnIndexOrThrow(TITLE))
                val titlePy = cursor.getString(cursor.getColumnIndexOrThrow(TITLE_PY))
                val is_sub = cursor.getInt(cursor.getColumnIndexOrThrow(IS_SUB))
                subChannels.add(TitleInfo(id, title, titlePy, is_sub))
            } while (cursor.moveToNext())
        }

        cursor.close()
        db.close()
        return subChannels
    }
}